import sys
import json
import pymysql
import logging
import yaml
import pandas as pd
from flask import Flask, render_template
from gevent import pywsgi

app = Flask(__name__)

# 读取dbinfo.json
def get_dbinfo(config: str):
    with open(config, "r") as f: 
        return json.load(f)

# 读取yml格式
def get_dbinfo_yaml(config: str):
    with open(config, encoding="utf-8") as f:
        data = f.read()
        return yaml.full_load(data)

# 查询数据表内容
def query_data():
    items_success = list()
    items_failure = list()
    for item in get_dbinfo_yaml(db_json_file):
        data = {}
        host = item['host']
        user=item['user']
        password=item['password']
        db=item['database']
        
        try:
            db = pymysql.connect(host=host, user=user, password=password, db=db,charset='utf8mb4')
            logging.info(f"mysql://{user}:{password}@tcp({host}:3306/{db}) CONNECT SUCCESS")
        except Exception as e:
            print(e)
            logging.error(f"mysql://{user}:{password}@tcp({host}:3306/{db}) {e}")
            continue
        field = ""
        for ele in fields:
            field += f"{ele}, "
        #field = field.removesuffix(", ") # 去掉末尾的"，"
        field = field.rstrip(", ") # 去掉末尾的"，"
        cursor = db.cursor()
        sql = f"""select {field} from { item['table'] } where success = 0"""
        cursor.execute(sql)
        content = cursor.fetchall()

        data['name'] = item['name']
        data['dsn'] = f"{item['host']}:3306/{item['database']}"
        data['table'] = item['table']
        data['sql'] = sql
        data['fields'] = fields
        data['content'] = content
        data['is_succ'] = True
        if len(content) == 0:
            data['msg'] = "flyway执行成功"
            items_success.append(data)
        else: 
            data['is_succ'] = False
            data['msg'] = "flyway执行失败"
            items_failure.append(data)
        logging.info(f"dsn: {data['dsn']} | table: {data['table']} | sql: {sql} | state: {data['is_succ']}")
        # 关闭数据库连接
        cursor.close()
        db.close()
    return items_success, items_failure

@app.route('/')
def index():
    data_success, data_failure = query_data()
    return render_template('index.html', data_success=data_success, data_failure=data_failure)

if __name__ == '__main__':
    log_file = 'log.log'

    # 定义日志
    logging.basicConfig(level=logging.INFO,
                      format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                       datefmt='%Y-%m-%d %H:%M:%S',
                       filename=log_file,
                       filemode='a')

    if len(sys.argv) == 2:
        db_json_file = sys.argv[1]
    else:
        print(f"example: python {sys.argv[0]} db.yaml")
        exit(1)
    logging.info(f"Load dbinfo: {db_json_file}")
    
    fields = ["installed_rank","version","type","script","installed_by","installed_on","execution_time","success"]
    
    # app.run(debug=False)
    server = pywsgi.WSGIServer(('0.0.0.0', 5000), app)
    server.serve_forever()
